package org.oschina.api.user.mail.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.oschina.Constants;
import org.oschina.api.BaseDAO;
import org.oschina.api.user.mail.model.UserMail;
import org.oschina.api.user.mail.vo.UserMailCountVo;
import org.oschina.api.user.mail.vo.UserMailTypeVo;
import org.oschina.api.user.mail.vo.UserMailVo;
import org.oschina.api.user.user.vo.UserVo;
import org.oschina.helper.cache.ICachePipeline;
import org.oschina.helper.messagequeue.IMessageQueueHelper;
import org.oschina.helper.query.IQueryCacheCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class UserMailDAO extends BaseDAO {
	
	@Autowired
	private IMessageQueueHelper messageQueueHelper;
	@Autowired
	private UserMailTypeDAO userMailTypeDAO;
	@Autowired
	private UserMailRecordDAO userMailRecordDAO;
	
	private final String findVo="SELECT usm.id,usm.title,usm.role,usm.content,us.id as 'author.id',us.username as 'author.username',us.headphoto as 'author.headphoto',usm.target as 'target.id',usm.type,usm.create_time,IFNULL((SELECT usmr.id FROM "+Constants.DB_USER_MAIL_RECORD+" usmr WHERE usmr.mail=usm.id AND usmr.user=?),0) as 'read' " +
			" FROM "+Constants.DB_USER_MAIL+" usm,"+Constants.DB_USER+" us" +
			" WHERE usm.author=us.id";
	
	private final String findVoByTargetAndRoleAndType=findVo+" AND ((usm.target=? OR usm.target=0) AND usm.type=? AND (usm.role=? OR usm.role=0)) ORDER BY usm.create_time DESC LIMIT ?,?";
	/**
	 * 根据目标和类型还有角色返回
	 * @param target
	 * @param role
	 * @param type
	 * @param first
	 * @param max
	 * @return
	 */
	public List<UserMailVo> findVoByTargetAndRoleAndType(final int target,final int role,final int type,int first,int max)
	{
		List<UserMailVo> result=getQueryHelper().query_cache(getCacheByTypeAndUser(target, type),Constants.TIMEOUT_USER_MAIL, Constants.MAX_CACHE_USER_MAIL, first, max, UserMailVo.class, new IQueryCacheCallback()
		{
			@Override
			public List get(int first, int max)
			{
				List<UserMailVo> result=getQueryHelper().query(findVoByTargetAndRoleAndType, new Object[]{target,target,type,role,first,max}, UserMailVo.class);
				for(UserMailVo userMailVo:result)
					userMailRecordDAO.updateRecord(target,userMailVo.getId(),userMailVo.getRead());
				return result;
			}
		});
		
		Iterator<UserMailVo> iterator=result.iterator();
		while(iterator.hasNext())
		{
			UserMailVo userMailVo=iterator.next();
			if(userMailVo!=null)
				userMailVo.setRead(userMailRecordDAO.isRead(target,userMailVo.getId()));
			else
				iterator.remove();
		}
		return result;
	}
	public void pushByUserAndType(int user,int type,int id)
	{
		getCacheHelper().lpush(getCacheByTypeAndUser(user,type), id, Constants.TIMEOUT_USER_MAIL);
	}
	private String getCacheByTypeAndUser(int user,int type)
	{
		return Constants.CACHE_USER_MAIL+"_"+user+"_"+type;
	}
	public UserMailVo findVoById(int id)
	{
		return findByCache(id);
	}
	private UserMailVo findByCache(int id)
	{
		return (UserMailVo)getCacheHelper().hget(getIdKey(id), UserMailVo.class);
	}
	public void putCache(UserMail userMail)
	{
		if(getCacheHelper().exists(getIdKey(userMail.getId())))
			return;
		
		UserVo newAuthor=new UserVo();
		newAuthor.setId(userMail.getAuthor().getId());
		newAuthor.setUsername(userMail.getAuthor().getUsername());
		newAuthor.setHeadphoto(userMail.getAuthor().getHeadphoto());
		
		userMail.setAuthor(newAuthor);
		userMail.setTarget(new UserVo(userMail.getTarget().getId()));
		getCacheHelper().hset(getIdKey(userMail.getId()), userMail, UserMail.class, Constants.TIMEOUT_USER_MAIL);
	}
	public void putCache(UserMailVo userMailVo)
	{
		getCacheHelper().hset(getIdKey(userMailVo.getId()), userMailVo, UserMailVo.class, Constants.TIMEOUT_USER_MAIL);
	}
	
	private final String findByType="SELECT usm.id,usm.title,usm.role,usm.content,us.id as 'author.id',us.username as 'author.username',us.headphoto as 'author.headphoto',usm.type,usm.create_time FROM "+Constants.DB_USER_MAIL+" usm ,"+Constants.DB_USER+" us WHERE usm.author=us.id AND usm.type=? ORDER BY usm.create_time DESC LIMIT ?,?";
	/**
	 * 根据类型返回
	 * @param type
	 * @param first
	 * @param max
	 * @return
	 */
	public List<UserMailVo> findByType(int type,int first,int max)
	{
		return getQueryHelper().query(findByType, new Object[]{type,first,max}, UserMailVo.class);
	}
	
	/**
	 * 根据类型返回数量
	 * @param type
	 * @return
	 */
	public int getCountByType(int type)
	{
		return (int) getQueryHelper().queryDataSum(new String[]{"type=?"}, new Object[]{type}, UserMailVo.class);
	}
	
	/**
	 * 清除用户邮件缓存
	 * @param user
	 */
	public void removeCache(int user)
	{
		List<UserMailTypeVo> types=userMailTypeDAO.findVo();
		ICachePipeline pipeline=getCacheHelper().getPipeline();
		for(UserMailTypeVo type:types)
		{
			pipeline.del(getCacheByTypeAndUser(user,type.getId()));
			List<Integer> typeKeys=getCacheHelper().lget(getCacheByTypeAndUser(user, type.getId()), 0, -1, Integer.class);
			if(typeKeys!=null && !typeKeys.isEmpty())
			{
				for(Integer id:typeKeys)
				{
					if(!"0".equals(getCacheHelper().hget(getIdKey(id), "target", String.class)))
						pipeline.del(getIdKey(id));
				}
			}
		}
		
		pipeline.del(getCountCache(user));
		pipeline.del(getUnReadCache(user));
		
		pipeline.sync();
		userMailRecordDAO.removeCache(user);
	}
	
	/**
	 * 根据目标和类型还有角色返回数量
	 * @param target
	 * @param role
	 * @param type
	 * @return
	 */
	public int getCountByTargetAndType(int target,int role,int type)
	{
		Integer result=getCountCacheByUserAndType(target,type);
		if(result==null)
		{
			result=(int) getQueryHelper().queryDataSum(new String[]{"((target=? OR target=0) AND (role=? OR role=0))","type=?"}, 
					new Object[]{target,role,type}, UserMailVo.class);
			setCountByType(target,type,result);
		}
		return result;
	}
	private Integer getCountCacheByUserAndType(int user,int type)
	{
		return (Integer)getCacheHelper().hget(getCountCache(user), type, Integer.class);
	}
	public void setCountByType(int user,int type,int count)
	{
		getCacheHelper().hset(getCountCache(user), type, count,Constants.TIMEOUT_USER_MAIL_COUNT);
	}
	public void updateCountByType(int user,int type,int count)
	{
		getCacheHelper().hincr(getCountCache(user), type, count);
	}
	private String getCountCache(int user)
	{
		return Constants.CACHE_USER_MAIL_COUNT+"_"+user;
	}
	
	private static final String getUnReadCount="SELECT usmt.id as 'type',"+
		"(SELECT COUNT(us.id) FROM "+Constants.DB_USER_MAIL+" us WHERE ((us.target=? OR us.target=0) AND (us.role=? OR us.role=0)) AND us.type=usmt.id AND "+
			"(SELECT usmr.id FROM "+Constants.DB_USER_MAIL_RECORD+" usmr WHERE usmr.mail=us.id) IS NULL) as 'count'"+
				"FROM "+Constants.DB_USER_MAIL_TYPE+" usmt";
	/**
	 * 根据用户和权限返回未读邮件数量
	 * @param user
	 * @param role
	 * @return
	 */
	public List<UserMailCountVo> getUnReadCount(int user,int role)
	{
		String cache=getUnReadCache(user);
		if(!getCacheHelper().exists(cache))
		{
			List<UserMailCountVo> userMailCountList=getQueryHelper().query(getUnReadCount, new Object[]{user,role}, UserMailCountVo.class);
			for(UserMailCountVo userMailCountVo:userMailCountList)
				setUnReadCount(user,userMailCountVo.getType(),userMailCountVo.getCount());
		}
		
		List<UserMailCountVo> result=new ArrayList<UserMailCountVo>();
		Map<String,String> values=getCacheHelper().hgetAll(getUnReadCache(user));
		Set<String> types=values.keySet();
		for(String type:types)
		{
			UserMailCountVo userMailCountVo=new UserMailCountVo();
			userMailCountVo.setType(Integer.valueOf(type));
			userMailCountVo.setCount(Integer.valueOf(values.get(type)));
			userMailCountVo.setMessage(userMailTypeDAO.findById(Integer.valueOf(type)).getText());
			result.add(userMailCountVo);
		}
		return result;
	}
	
	/**
	 * 更新未读数量
	 * @param user
	 * @param type
	 * @param count
	 */
	public void updateUnReadCount(int user,int type,int count)
	{
		getCacheHelper().hincr(getUnReadCache(user), type, count);
	}
	public void setUnReadCount(int user,int type,int count)
	{
		getCacheHelper().hset(getUnReadCache(user), type, count,Constants.TIMEOUT_USER_MAIL_COUNT);
	}
	private String getUnReadCache(int user)
	{
		return Constants.CACHE_USER_MAIL_UNREAD_COUNT+"_"+user;
	}
	
	/**
	 * 删除
	 * @param userMailVo
	 * @return
	 */
	public int delete(UserMailVo userMailVo)
	{
		getCacheHelper().del(getIdKey(userMailVo.getId()));
		updateCountByType(userMailVo.getTarget().getId(), userMailVo.getType(), -1);
		userMailRecordDAO.deleteByMail(userMailVo.getTarget().getId(),userMailVo.getId());
		return (int) getQueryHelper().delete(new String[]{"id=?"}, new Object[]{userMailVo.getId()}, UserMailVo.class);
	}
	
	/**
	 * 创建
	 * @param userMail
	 * @return
	 */
	public UserMail create(UserMail userMail)
	{
		messageQueueHelper.send(Constants.QUEUE_USER_MAIL_PUSH, userMail);
		getQueryHelper().insert(new String[]{"id","title","content","author","target","type","role","create_time"}, 
				new Object[]{userMail.getId(),userMail.getTitle(),userMail.getContent(),userMail.getAuthor().getId(),userMail.getTarget().getId(),userMail.getType(),userMail.getRole(),userMail.getCreate_time()}, UserMailVo.class);
		return userMail;
	}
	
	private String getIdKey(Object id)
	{
		return getKey(Constants.CACHE_USER_MAIL,id);
	}
}